﻿

CREATE proc [Projects].[SaveProject]
@ProjectID			int = null output, 
@Intern				bit,
@ProjectStatus		tinyint,
@MatchCode			nvarchar(50), 
@PLZ				nchar(10),
@StandortID			int,
@CustomerSource		int,
@FirstContactDate	datetime2(7) = null, 
@ImplementationDate	datetime2(7) = null, 
@OfferDate			datetime2(7) = null, 
@OrderDate			datetime2(7) = null,
@FinishDateProjected	datetime2(7) = null,
@FinishDateReal		datetime2(7) = null, 
@FilePath			nvarchar(255),
@CalculationFileName	nvarchar(100),
@Distance			decimal(11,2),
@UserID				int,
@ProjectContacts [dbo].[udtProjectContacts] readonly



as
begin
	set nocount on

	declare @rc int,
			@now datetime2(7) = sysdatetime(),
			@logText nvarchar(150),
			@SetTran bit = 0;

	if @MatchCode is null
	begin
		Select @ProjectID 'ProjectID', -1 'Return', 'ShortName is null' 'Error' 
		goto WithError
	end

	if @FilePath is not null and right(rtrim(@FilePath), 1) != '\'
	begin
		set @FilePath += '\'
	end

	set xact_abort on
	
	if @@TRANCOUNT = 0
	begin
		begin tran
		set @SetTran = 1;
	end
	
	begin try
		
		;with sel
		as
		(
			select	@ProjectID			'ProjectID',
					@Intern				'Intern',
					@ProjectStatus		'ProjectStatus',
					@MatchCode			'MatchCode', 
					@PLZ				'PLZ', 
					@StandortID			'StandortID', 
					@CustomerSource		'CustomerSource', 
					@FirstContactDate	'FirstContactDate', 
					@ImplementationDate	'ImplementationDate', 
					@OfferDate			'OfferDate', 
					@OrderDate			'OrderDate',
					@FinishDateProjected	'FinishDateProjected',
					@FinishDateReal		'FinishDateReal',
					@now				'CreateOn',
					@UserID				'CreateBy',
					@now				'LastChangedOn',
					@UserID				'LastChangedBy',
					@FilePath			'Filepath', 
					@CalculationFileName 'CalculationFileName',
					@Distance			'Distance'
					
		)
		merge into Projects.datProjects t
		using sel s on
			s.ProjectID = t.ProjectID
		when not matched by target then
		insert (
				[Intern], 
				ProjectStatus, 
				MatchCode, 
				PLZ, 
				StandortID, 
				CustomerSource, 
				FirstContactDate, 
				ImplementationDate, 
				OfferDate, 
				OrderDate, 
				FinishDateProjected, 
				FinishDateReal, 
				CreateOn, 
				CreateBy, 
				LastChangedOn, 
				LastChangedBy, 
				FilePath, 
				CalculationFileName,
				Distance
				)
		values (
				s.[Intern],
				s.ProjectStatus, 
				s.MatchCode, 
				s.PLZ, 
				s.StandortID, 
				s.CustomerSource, 
				s.FirstContactDate, 
				s.ImplementationDate, 
				s.OfferDate, 
				s.OrderDate, 
				s.FinishDateProjected, 
				s.FinishDateReal, 
				s.CreateOn, 
				s.CreateBy, 
				s.LastChangedOn, 
				s.LastChangedBy, 
				s.FilePath,
				s.CalculationFileName,
				s.Distance
				)
		when matched then
		update
			set
				[Intern]				= s.[Intern],
				ProjectStatus			= s.ProjectStatus, 
				MatchCode				= s.MatchCode, 
				PLZ						= s.PLZ, 
				StandortID				= s.StandortID, 
				CustomerSource			= s.CustomerSource, 
				FirstContactDate		= s.FirstContactDate, 
				ImplementationDate		= s.ImplementationDate, 
				OfferDate				= s.OfferDate, 
				OrderDate				= s.OrderDate, 
				FinishDateProjected		= s.FinishDateProjected, 
				FinishDateReal			= s.FinishDateReal,
				LastChangedOn			= s.LastChangedOn, 
				LastChangedBy			= s.LastChangedBy, 
				FilePath				= s.FilePath,
				CalculationFileName		= s.CalculationFileName,
				Distance				= s.Distance
				;

		if @@ERROR != 0
		begin
			print 'Error!!'
			print ERROR_MESSAGE()
		end

		if coalesce(@ProjectID,0) = 0
		begin
			set @ProjectID =SCOPE_IDENTITY();
		end

		;with contacts
		as
		(
			select	@ProjectID 'ProjectID',
					c.ContactID,
					c.ContactType,
					c.Recipient,
					c.RecipientAddtional,
					c.EPLZ,
					c.Straße
			from	@ProjectContacts c
		)
		merge into [Projects].[mnProjectContact] t
		using contacts s on
			s.ProjectID = t.ProjectID
			and s.ContactID = t.ContactID
		when not matched then 
			insert (ProjectID, ContactID, ContactType, Recipient, RecipientAddtional, EPLZ, Straße)
			values (
				s.ProjectID, 
				s.ContactID, 
				s.ContactType, 
				s.Recipient, 
				s.RecipientAddtional, 
				s.EPLZ, 
				s.Straße)
		when matched then 
			update
				set	ContactType			= s.ContactType, 
					Recipient			= s.Recipient, 
					RecipientAddtional	= s.RecipientAddtional, 
					EPLZ				= s.EPLZ,
					Straße				= s.Straße
		when not matched by source
			and t.ProjectID = @ProjectID
			then 
				delete
		;
		
		if @@ERROR != 0
		begin
			print 'Error!!'
			print ERROR_MESSAGE()
		end

	end try
	begin catch
		Select @ProjectID 'ProjectID', -1 'Return', case when @@error != 0 then ERROR_MESSAGE() else '' end 'Error' 
		goto WithError
	end catch

	select @ProjectID 'ProjectID', 0 'Return', null 'Error'

	if @SetTran = 1
	begin
		commit tran
	end
	
	return 0

WithError:
	--set @logText = 'Projects.SaveProject: ' + cast(@ProjectID as char(10))
	--	exec @rc = dbo.SaveLog
	--		@UserID = @UserID,
	--		@LogType = 'S',
	--		@LogArea = 'Pr',
	--		@LogDetails = @logText,
	--		@IsError = 1

	if @SetTran = 1
	begin
		
		rollback tran
	end
	return -1
end